package com.cpi.cornpp.version.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.version.dto.MonthPeriodVersionDTO;
import com.cpi.cornpp.version.dto.VersionDTO;
import com.cpi.cornpp.version.exception.VersionException;
import com.cs.common.init.SystemParameterCollection;
import com.cs.common.init.beans.SystemParameterBean;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

public class VersionDAO extends DBDAO {
	private static final String SQL_SELECT_VERSION = "SELECT version.version_id, version.related_version_id, parent.short_name, parent.description as parentDescription, parent.start_date as parentStartDate, parent.end_date as parentEndDate, version.short_name, version.description, version.start_date, version.end_date, version.status FROM versions version LEFT JOIN versions parent on version.related_version_id = parent.version_id";
	private static final String SQL_EXECUTE_SP_CREATE_VERSION = "EXEC sp_Create_Version_Full ?, ?, ?, ?";
	private static final String SQL_UPDATE_VERSION = "UPDATE versions SET short_name = ?, description = ?, status = ? FROM versions version";
	private static final String SQL_DELETE_VERSION = "DELETE from versions where version_id = ?";//$NON-NLS-1$
	private static final String SQL_SELECT_MONTH_PERIOD_VERSION = "select month_period_version_id, short_name, year from months_periods_versions mpv inner join months_periods mp on mpv.month_period_id = mp.month_period_id where version_id = ? order by mp.start_date";//$NON-NLS-1$
	private static final String SQL_SELECT_MONTH_PERIOD_VERSION_BY_ID = "select month_period_version_id, short_name, year from months_periods_versions mpv inner join months_periods mp on mpv.month_period_id = mp.month_period_id where version_id = ?  and month_period_version_id = ? order by mp.start_date";//$NON-NLS-1$

	public List list(int orderByColumn, boolean ascendingOrder) throws Exception {
		StringBuffer sql = null;
		ArrayList arrayResult = null;
		PreparedStatement preparedStatement = null;
		VersionDTO versionDTO = null;
		Date parentStartDate;
		Date parentEndDate;

		sql = new StringBuffer(SQL_SELECT_VERSION);
		sql.append(SQL_WHERE);
		sql.append(VersionDTO.STATUS);
		sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
		sql.append(SQL_ORDER_BY);
		
		switch (orderByColumn) {
		case 0:
			sql.append(VersionDTO.START_DATE);
			break;
		case 1:
			sql.append(VersionDTO.END_DATE);
			break;
		case 2:
			sql.append(VersionDTO.SHORT_NAME);
			break;
		case 3:
			sql.append(VersionDTO.DESCRIPTION);
			break;
		case 4:
			sql.append(VersionDTO.PARENT_SHORT_NAME);
			break;
		}
		if (ascendingOrder) {
			sql.append(SQL_ASCENDING_ORDER);
		} else {
			sql.append(SQL_DESCENDING_ORDER);
		}
		
		try {
			
			arrayResult = new ArrayList(0);
			preparedStatement = prepareSQL(sql.toString(), dsName);
			preparedStatement.setInt(1, 1);
			ResultSet rst = executeQuery(preparedStatement);
			
			while (rst.next()) {
				versionDTO = new VersionDTO();
				versionDTO.setVersionId(rst.getInt(1));
				versionDTO.setParentId(rst.getInt(2));
				versionDTO.setParentShortName(rst.getString(3));
				versionDTO.setParentDescription(rst.getString(4));
				Calendar calP1 = Calendar.getInstance();
				parentStartDate = rst.getDate(5);
				if (parentStartDate != null) 
				{
					calP1.setTime(parentStartDate);
					versionDTO.setParentStartDate(calP1);
				}
				Calendar calP2 = Calendar.getInstance();
				parentEndDate = rst.getDate(6);
				if (parentEndDate != null)
				{
					calP2.setTime(rst.getDate(6));
					versionDTO.setParentEndDate(calP2);
				}
				versionDTO.setShortName(rst.getString(7));
				versionDTO.setDescription(rst.getString(8));
				Date startDate = rst.getDate(9);
				Calendar cal = Calendar.getInstance();
				cal.setTime(startDate);
				versionDTO.setStartDate(cal);
				Date endDate = rst.getDate(10);
				Calendar cal2 = Calendar.getInstance();
				cal2.setTime(endDate);
				versionDTO.setEndDate(cal2);
				Boolean status = Boolean.valueOf(rst.getBoolean(11));
				versionDTO.setStatus(status);
				arrayResult.add(versionDTO);
			}
			 
		} catch (SQLException sqlException) {
			VersionException versionException = null;
			sqlException.printStackTrace(System.out);
			versionException = new VersionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw versionException;
		} finally {
			
			try {
				
				closeConnection(preparedStatement.getConnection());
				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		
		return arrayResult;
	}
	
	public VersionDTO getVersionById(Integer versionId) throws VersionException{
		VersionDTO versionDTO = null;
		StringBuffer sql = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Date parentStartDate;
		Date parentEndDate;
		
		try{
			
			if (versionId != null){
				sql = new StringBuffer(SQL_SELECT_VERSION);
				
				sql.append(SQL_WHERE);
				sql.append(VersionDTO.ID);
				sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);	
				
				pstm = prepareSQL(sql.toString(), dsName);
				
				pstm.setObject(1, versionId);
				
				rs = executeQuery(pstm);
				
				if ((rs != null) && (rs.next())){
					versionDTO = new VersionDTO();
					versionDTO.setVersionId(rs.getInt(1));
					versionDTO.setParentId(rs.getInt(2));
					versionDTO.setParentShortName(rs.getString(3));
					versionDTO.setParentDescription(rs.getString(4));
					Calendar calP1 = Calendar.getInstance();
					parentStartDate = rs.getDate(5);
					if (parentStartDate != null) 
					{
						calP1.setTime(parentStartDate);
						versionDTO.setParentStartDate(calP1);
					}
					Calendar calP2 = Calendar.getInstance();
					parentEndDate = rs.getDate(6);
					if (parentEndDate != null)
					{
						calP2.setTime(rs.getDate(6));
						versionDTO.setParentEndDate(calP2);
					}
					versionDTO.setShortName(rs.getString(7));
					versionDTO.setDescription(rs.getString(8));
					Date startDate = rs.getDate(9);
					Calendar cal = Calendar.getInstance();
					cal.setTime(startDate);
					versionDTO.setStartDate(cal);
					Date endDate = rs.getDate(10);
					Calendar cal2 = Calendar.getInstance();
					cal2.setTime(endDate);
					versionDTO.setEndDate(cal2);
					Boolean status = Boolean.valueOf(rs.getBoolean(11));
					versionDTO.setStatus(status);
				}
			}
			
		} catch(PoolException poe){
			VersionException versionException = null;
			poe.printStackTrace(System.out);
			versionException = new VersionException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw versionException;
		} catch (SQLException sqlException) {
			VersionException versionException = null;
			sqlException.printStackTrace(System.out);
			versionException = new VersionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw versionException;
		} finally {
			
			try {
				
				closeConnection(pstm.getConnection());
				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		
		return versionDTO;
	}
	
	public int insertVersion(VersionDTO versionDTO) throws VersionException{
		int insertedRows = 0;
		PreparedStatement pstm = null;
		StringBuffer sql = null;
		try{
			sql = new StringBuffer(0);
			if (versionDTO != null){
				sql.append(SQL_EXECUTE_SP_CREATE_VERSION);
				if( versionDTO.getParentId() > 0 ){
					sql.append(SQL_COMMA);
					sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN);
					sql.append(SQL_COMMA);
					sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN);
				}
				pstm = prepareSQL(sql.toString(), dsName);
				pstm.setString(1, versionDTO.getShortName());
				pstm.setString(2, versionDTO.getDescription());
				pstm.setObject(3, versionDTO.getStartDate().getTime());
				pstm.setObject(4, versionDTO.getEndDate().getTime());
				if( versionDTO.getParentId() > 0 ){
					pstm.setInt(5, versionDTO.getParentId());
					pstm.setString(6, versionDTO.getRelatedType());
				}
				
				insertedRows = executeInsert(pstm);
			}
			
		} catch(PoolException poe){
			VersionException vee = null;
			poe.printStackTrace(System.out);
			vee = new VersionException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw vee;
		} catch(SQLException sqle){
			VersionException vee = null;
			sqle.printStackTrace(System.out);
			vee = new VersionException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw vee;
		} finally {
			
			try {
				
				closeConnection(pstm.getConnection());
				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		
		return insertedRows;
	}
	
	public int updateVersionById(VersionDTO versionDTO) throws VersionException{
		int updatedRows = 0;
		PreparedStatement pstm = null;
		StringBuffer sql = null;
		
		try{
			
			if (versionDTO != null){
				sql = new StringBuffer(SQL_UPDATE_VERSION);
				
				sql.append(SQL_WHERE);
				sql.append(VersionDTO.ID);
				sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
				
				pstm = prepareSQL(sql.toString(), dsName);
				
				pstm.setString(1, versionDTO.getShortName());
				pstm.setString(2, versionDTO.getDescription());
				pstm.setInt(3, versionDTO.getStatus().booleanValue() ? 1 : 0);
				pstm.setInt(4, versionDTO.getVersionId());
				
				updatedRows = executeUpdate(pstm);
			}
			
		} catch(PoolException poe){
			VersionException vee = null;
			poe.printStackTrace(System.out);
			vee = new VersionException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw vee;
		} catch(SQLException sqle){
			VersionException vee = null;
			sqle.printStackTrace(System.out);
			vee = new VersionException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw vee;
		} finally {
			
			try {
				
				closeConnection(pstm.getConnection());
				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		
		return updatedRows;
	}
	
	/**
	 * Delete a version
	 * @param versionId
	 * @return TRUE : Record deleted , FALSE: deletion fails 
	 * @throws VersionException
	 */
	public void delete(int versionId) throws VersionException{
		int deletedRows = 0;
		PreparedStatement preparedStatement = null;
		SystemParameterCollection systemParameterCollection = null;
		SystemParameterBean systemParameterBean = null;
		try{
			preparedStatement = prepareSQL(SQL_DELETE_VERSION, dsName);
			preparedStatement.setInt(1, versionId);
			deletedRows = executeUpdate(preparedStatement);

			if (deletedRows <= 0 ) {
				VersionException versionException = null;
				versionException = new VersionException (MSG_NO_DELETE,CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
				throw versionException;
			}
		} catch(SQLException sqle){
			VersionException versionException = null;
			systemParameterCollection = SystemParameterCollection.getInstance();
			systemParameterBean = systemParameterCollection.getSystemParameterBean(DBDAO.FOREIGN_KEY_ERROR_CODE);
			if(sqle.getErrorCode() == systemParameterBean.getIntValue()){
				versionException = new VersionException (DBDAO.FOREIGN_KEY_ERROR_KEY, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			}else{
				versionException = new VersionException (sqle, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			}
			sqle.printStackTrace(System.out);
			throw versionException;
		}catch (PoolException poolException){
			VersionException versionException = null;
			poolException.printStackTrace(System.out);
			versionException = new VersionException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			throw versionException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}	
	}
	/**
	 * @author Edgar Joao
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public List getVersionsPeriods(int versionId) throws VersionException{
		List listOfVersionsPeriods = null;
		PreparedStatement preparedStatement = null;
		MonthPeriodVersionDTO monthPeriodVersionDTO = null;
		ResultSet rs = null;
		try{				
			listOfVersionsPeriods = new ArrayList(0);			
			preparedStatement = prepareSQL(SQL_SELECT_MONTH_PERIOD_VERSION, dsName);
			preparedStatement.setInt(1, versionId);	
			rs = executeQuery(preparedStatement);			
			while (rs.next()){
				monthPeriodVersionDTO = new MonthPeriodVersionDTO();
				monthPeriodVersionDTO.setId(rs.getInt(1));
				monthPeriodVersionDTO.setShortName(rs.getString(2));
				monthPeriodVersionDTO.setYear(rs.getInt(3));
				listOfVersionsPeriods.add(monthPeriodVersionDTO);
			}	
		}catch (PoolException poolException){
			VersionException versionException = null;
			poolException.printStackTrace(System.out);
			versionException = new VersionException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw versionException;	
		} catch (SQLException sqlException) {
			VersionException versionException = null;
			sqlException.printStackTrace(System.out);
			versionException = new VersionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw versionException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return listOfVersionsPeriods;
	}
	
	public List getVersionsPeriods(int versionId, int periodId) throws VersionException{
		List listOfVersionsPeriods = null;
		PreparedStatement preparedStatement = null;
		MonthPeriodVersionDTO monthPeriodVersionDTO = null;
		ResultSet rs = null;
		try{				
			listOfVersionsPeriods = new ArrayList(0);			
			preparedStatement = prepareSQL(SQL_SELECT_MONTH_PERIOD_VERSION_BY_ID, dsName);
			preparedStatement.setInt(1, versionId);	
			preparedStatement.setInt(2, periodId);
			rs = executeQuery(preparedStatement);			
			while (rs.next()){
				monthPeriodVersionDTO = new MonthPeriodVersionDTO();
				monthPeriodVersionDTO.setId(rs.getInt(1));
				monthPeriodVersionDTO.setShortName(rs.getString(2));
				monthPeriodVersionDTO.setYear(rs.getInt(3));
				listOfVersionsPeriods.add(monthPeriodVersionDTO);
			}	
		}catch (PoolException poolException){
			VersionException versionException = null;
			poolException.printStackTrace(System.out);
			versionException = new VersionException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw versionException;	
		} catch (SQLException sqlException) {
			VersionException versionException = null;
			sqlException.printStackTrace(System.out);
			versionException = new VersionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw versionException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return listOfVersionsPeriods;
	}
}